<!DOCTYPE html>



  


<html class="theme-next muse use-motion" lang="zh-CN">
<head><meta name="generator" content="Hexo 3.9.0">
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">
















  
  
  <link href="/hexo/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css">







<link href="/hexo/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/hexo/css/main.css?v=5.1.4" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/hexo/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/hexo/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/hexo/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/hexo/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="开发笔记,">










<meta name="description" content="最近好像到了这样一个阶段，就是开始广泛接触过或没接触过的一些技术应用到自己的项目中去，也算是一种补课和复习吧，把好多之前接触过却不熟，或者想了解却还没了解的一些技术重新整理，重新学习巩固了一番，有了一点新的认识，留博记录下。少罗嗦，先看东西 12345678910111213141516171819202122232425262728begin    declare @a int,@error i">
<meta name="keywords" content="开发笔记">
<meta property="og:type" content="article">
<meta property="og:title" content="先用起来再细研究——游标（一）">
<meta property="og:url" content="https://tony_df.coding.net/2018/12/18/先用起来再细研究——游标（一）/index.html">
<meta property="og:site_name" content="Tony&#39;s blog">
<meta property="og:description" content="最近好像到了这样一个阶段，就是开始广泛接触过或没接触过的一些技术应用到自己的项目中去，也算是一种补课和复习吧，把好多之前接触过却不熟，或者想了解却还没了解的一些技术重新整理，重新学习巩固了一番，有了一点新的认识，留博记录下。少罗嗦，先看东西 12345678910111213141516171819202122232425262728begin    declare @a int,@error i">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://tony_df.coding.net/2018/12/18/先用起来再细研究——游标（一）/%E6%B8%B8%E6%A0%87%E6%95%88%E6%9E%9C.png">
<meta property="og:updated_time" content="2019-10-26T00:55:02.429Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="先用起来再细研究——游标（一）">
<meta name="twitter:description" content="最近好像到了这样一个阶段，就是开始广泛接触过或没接触过的一些技术应用到自己的项目中去，也算是一种补课和复习吧，把好多之前接触过却不熟，或者想了解却还没了解的一些技术重新整理，重新学习巩固了一番，有了一点新的认识，留博记录下。少罗嗦，先看东西 12345678910111213141516171819202122232425262728begin    declare @a int,@error i">
<meta name="twitter:image" content="https://tony_df.coding.net/2018/12/18/先用起来再细研究——游标（一）/%E6%B8%B8%E6%A0%87%E6%95%88%E6%9E%9C.png">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/hexo/',
    scheme: 'Muse',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: 'Tony'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="https://tony_df.coding.net/2018/12/18/先用起来再细研究——游标（一）/">





  <title>先用起来再细研究——游标（一） | Tony's blog</title>
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-CN">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/hexo/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Tony's blog</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle">学着码代码,学着码人生;</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/hexo/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br>
            
            Home
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/hexo/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br>
            
            Tags
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/hexo/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br>
            
            Archives
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://tony_df.coding.net/hexo/2018/12/18/先用起来再细研究——游标（一）/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Tony">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/hexo/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Tony's blog">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">先用起来再细研究——游标（一）</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">Posted on</span>
              
              <time title="Post created" itemprop="dateCreated datePublished" datetime="2018-12-18T10:00:06+08:00">
                2018-12-18
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <p>最近好像到了这样一个阶段，就是开始广泛接触过或没接触过的一些技术应用到自己的项目中去，也算是一种补课和复习吧，把好多之前接触过却不熟，或者想了解却还没了解的一些技术重新整理，重新学习巩固了一番，有了一点新的认识，留博记录下。<br>少罗嗦，先看东西</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">begin</span></span><br><span class="line">    <span class="keyword">declare</span> @a <span class="built_in">int</span>,@<span class="keyword">error</span> <span class="built_in">int</span>    </span><br><span class="line">    <span class="keyword">declare</span> @coupon_id <span class="built_in">int</span>,@merchant_id <span class="built_in">int</span></span><br><span class="line"></span><br><span class="line">    <span class="keyword">set</span> @a=<span class="number">1</span></span><br><span class="line">    <span class="keyword">set</span> @<span class="keyword">error</span>=<span class="number">0</span></span><br><span class="line">    <span class="comment">--申明游标为Uid</span></span><br><span class="line">    <span class="keyword">declare</span> coupon_cursor <span class="keyword">cursor</span></span><br><span class="line">    <span class="keyword">for</span> (<span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">as</span> coupon_id,merchant_id <span class="keyword">from</span> dt_merchant_coupons )</span><br><span class="line">    <span class="comment">--打开游标--</span></span><br><span class="line">    <span class="keyword">open</span> coupon_cursor</span><br><span class="line">    <span class="comment">--开始循环游标变量--</span></span><br><span class="line">    <span class="keyword">fetch</span> <span class="keyword">next</span> <span class="keyword">from</span> coupon_cursor <span class="keyword">into</span> @coupon_id,@merchant_id</span><br><span class="line">    <span class="keyword">while</span> @@FETCH_STATUS = <span class="number">0</span>    <span class="comment">--返回被 FETCH语句执行的最后游标的状态--</span></span><br><span class="line">        <span class="keyword">begin</span></span><br><span class="line">      			print <span class="string">'优惠券id:'</span>+<span class="keyword">cast</span>(@coupon_id <span class="keyword">as</span> <span class="built_in">varchar</span>)</span><br><span class="line">      			print <span class="string">'商家id:'</span>+<span class="keyword">cast</span>(@merchant_id <span class="keyword">as</span> <span class="built_in">varchar</span>)			</span><br><span class="line">      			print <span class="string">'第'</span>+<span class="keyword">cast</span>(@a <span class="keyword">as</span> <span class="built_in">varchar</span>)+<span class="string">'条'</span></span><br><span class="line">      			print <span class="string">'--------------------'</span></span><br><span class="line">            <span class="comment">--update dt_user_coupons set merchant_id=@merchant_id where coupon_id=@coupon_id --更新所需的字段</span></span><br><span class="line">            <span class="keyword">set</span> @a=@a+<span class="number">1</span></span><br><span class="line">            <span class="keyword">set</span> @<span class="keyword">error</span>= @<span class="keyword">error</span> + @@<span class="keyword">ERROR</span>   <span class="comment">--记录每次运行sql后是否正确，0正确</span></span><br><span class="line">            <span class="keyword">fetch</span> <span class="keyword">next</span> <span class="keyword">from</span> coupon_cursor <span class="keyword">into</span> @coupon_id,@merchant_id   <span class="comment">--转到下一个游标，没有会死循环            </span></span><br><span class="line">        <span class="keyword">end</span>    </span><br><span class="line">    <span class="keyword">close</span> coupon_cursor  <span class="comment">--关闭游标</span></span><br><span class="line">    <span class="keyword">deallocate</span> coupon_cursor   <span class="comment">--释放游标</span></span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"><span class="keyword">go</span></span><br></pre></td></tr></table></figure>

<p>来看看它执行的效果（只看效果，我把update注释掉了）<br><img src="%E6%B8%B8%E6%A0%87%E6%95%88%E6%9E%9C.png" alt="游标效果"></p>
<p>我是在实际项目中遇到了这样的问题，有三张表，用户券表，券表，商家表，现在项目对三者的联系是，商家和券表关联，用户券表和券表关联，本来这样的关系在实际项目中已经够用了，但为了更优的效率，我需要在用户券表里增加商家id的字段，而问题是现在表里的数据已经不少了，我也不能挨个去对着那个用户领的哪个券，再去找哪个券属于那个商家，然后在把id更新到用户券表里吧~<br>当然可以写段代码去执行，但我觉得还是直接操作数据库去做这个事儿比较合适，所以就想到了游标的方式，游标向行的是行的集合，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据，基于此，遇到的问题就完美解决啦<del>至于游标具体的内容，后续再聊吧</del></p>
<p>我发现，像我这种人，因该是很难，或很少有机会能静下心来，像个苦行僧似得去正儿八经的通过自学去掌握某样技术，除非有很大的利益驱动才会这样去做。但是，如果是在平时的项目里，真正遇到了自己能力范围解决不了的问题时，就会很有动力临时去学习，效率也是非常吓人，往往很快就能把问题解决，但这种靠一时热血吸收来的知识，往往会在问题解决后，很短的一段时间就会遗忘或生疏，所以为了防止自己能最大可能的吸收临时学来的知识，我开辟了这样一个系列~，记录下学习经验，盼大家雅正。<br>当我们临时去学习某项技术用以解决当前问题的时候，最大的一个期望就是，速成，而不管是大神的博客，某项技术的官方文档，还是像csdn，msdn，GitHub等这些码农聚集地，往往大家给出的例子或者介绍都非常全面，非常细致，甚至于啰嗦了一些，其实这也没办法，大家的水平都越来越高，给出的讲解当然细致入微，倾囊相授，那像我这样的，着急要用起来的小伙们就捉急了，跟着学吧，内容太多，项目赶不及，不跟着学吧，好像有时候也找不到更好的例子了，所以，咱的目标是先知其然，后知其所以然，电影《钢铁侠》里有这么一段，唐尼第一次穿着战甲往高空飞，他的人工智能助理贾维斯告诉他，还没做过测试，不要这么干，唐尼就说“先做在测”。<br>今天来说的游标，编程老炮们肯定对这东西比较熟悉，在ORM插件不这么丰富的过去，人们想实现复杂的数据操作，多数时候都要用到游标，而且，它也确实非常强大，只不过在今天的编程环境下，各类mvc框架的出现，伴随着诞生的也有一系列数据库操作框架，往往只需要简单的操作，简单的语法，就可以实现繁杂的数据操作，几乎可以替代以前常用的存储过程，触发器，游标之类的数据库知识。<br>而且现在出现了代码优先的方式，很多时候，我们是不需要操作数据库去修改表的结构的，而是通过data migration(拽个洋文)的方式，很面向对象，好了今天就聊这么多吧<br>（ORM框架简介：<a href="https://www.cnblogs.com/wisdo/p/4279091.html）" target="_blank" rel="noopener">https://www.cnblogs.com/wisdo/p/4279091.html）</a><br>（大神写的深入游标教程：<a href="https://www.cnblogs.com/selene/p/4480328.html）" target="_blank" rel="noopener">https://www.cnblogs.com/selene/p/4480328.html）</a></p>

      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/hexo/tags/开发笔记/" rel="tag"># 开发笔记</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/hexo/2018/12/10/先用起来再细研究——异步编程（一）/" rel="next" title="先用起来再系研究——异步编程（一）">
                <i class="fa fa-chevron-left"></i> 先用起来再系研究——异步编程（一）
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/hexo/2018/12/26/一次大数据量操作的方法实例/" rel="prev" title="一次大数据量操作的方法实例">
                一次大数据量操作的方法实例 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
        <!-- Go to www.addthis.com/dashboard to customize your tools -->
<div class="addthis_inline_share_toolbox">
  <script type="text/javascript" src="//s7.addthis.com/js/300/addthis_widget.js#pubid=ra-5bbb2338b87602f0" async="async"></script>
</div>

      
    </div>
  </div>


          </div>
          


          

  
    <div class="comments" id="comments">
      <div id="lv-container" data-id="city" data-uid="MTAyMC8zOTA1My8xNTU4MA"></div>
    </div>

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      

      <section class="site-overview-wrap sidebar-panel sidebar-panel-active">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <p class="site-author-name" itemprop="name">Tony</p>
              <p class="site-description motion-element" itemprop="description">学习总结</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/hexo/archives/">
              
                  <span class="site-state-item-count">58</span>
                  <span class="site-state-item-name">posts</span>
                </a>
              </div>
            

            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/hexo/tags/index.html">
                  <span class="site-state-item-count">7</span>
                  <span class="site-state-item-name">tags</span>
                </a>
              </div>
            

          </nav>

          

          

          
          

          
          

          

        </div>
      </section>

      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2021</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Tony</span>

  
</div>


  <div class="powered-by">Powered by <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a></div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">Theme &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Muse</a> v5.1.4</div>




        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/hexo/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/hexo/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/hexo/js/src/motion.js?v=5.1.4"></script>



  
  

  
  <script type="text/javascript" src="/hexo/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/hexo/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/hexo/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  
    <script type="text/javascript">
      (function(d, s) {
        var j, e = d.getElementsByTagName(s)[0];
        if (typeof LivereTower === 'function') { return; }
        j = d.createElement(s);
        j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
        j.async = true;
        e.parentNode.insertBefore(j, e);
      })(document, 'script');
    </script>
  












  





  

  

  

  
  

  

  

  

</body>
</html>
